// $codepro.audit.disable com.instantiations.assist.eclipse.analysis.unusedReturnValue, nullPointerDereference
/*
 * GameSave.java
 * Team qq 2011
 */
package com.google.code.timetrail.backend;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 * Saves the game in a database
 * 
 * @author Utkarsh
 * @version 1.1
 */
public class GameSave {
	/**
	 * Location of the database
	 */
	private static final String DATABASE_LOCATION = "saves/savegames.db";

	/**
	 * Name of the save folder
	 */
	private static final String SAVE_FOLDER = "saves";

	/**
	 * Save game table name string
	 */
	private static final String SAVEGAME_TABLE_NAME = "SAVED_GAMES";

	/**
	 * save game table creator string
	 */
	private static final String SAVEGAME_TABLE_CREATOR = "CREATE TABLE IF NOT EXISTS "
			+ SAVEGAME_TABLE_NAME
			+ " (NAME TEXT NOT NULL ,"
			+ " SAVE_DATA BLOB NOT NULL , CREATE_DATE DATETIME NOT NULL)";

	/**
	 * save game load string
	 */
	private static final String SAVEGAME_LOAD = "SELECT SAVE_DATA FROM "
			+ SAVEGAME_TABLE_NAME + " WHERE ";

	/**
	 * save game save string
	 */
	private static final String SAVEGAME_SAVE = "INSERT INTO "
			+ SAVEGAME_TABLE_NAME + " VALUES";

	/**
	 * save game delete string
	 */
	private static final String SAVEGAME_DELETE = "DELETE FROM "
			+ SAVEGAME_TABLE_NAME + " WHERE ";

	/**
	 * Save game get all entries string
	 */
	private static final String SAVEGAME_GET_ALL_ENTRIES = "SELECT NAME,CREATE_DATE FROM "
			+ SAVEGAME_TABLE_NAME;

	/**
	 * save game update entry string
	 */
	private static final String SAVEGAME_UPDATE_ENTRY = "UPDATE "
			+ SAVEGAME_TABLE_NAME + " SET ";

	/**
	 * save game file
	 */
	private static final File SAVE_GAME_FILE = new File(DATABASE_LOCATION);

	/**
	 * save game file directory
	 */
	private static final File SAVE_GAME_DIR = new File(SAVE_FOLDER);

	/**
	 * database current date
	 */
	private static final SimpleDateFormat SQLDATE = new SimpleDateFormat(
			"yyyy-MM-dd HH:mm:ss");

	/**
	 * SQL Timeout
	 */
	private static final int TIMEOUT = 20;

	/**
	 * This is the constructor for the game save class
	 * 
	 * @throws ClassNotFoundException a exception for where the class is not found
	 * @throws IOException exception with wrong input or output
	 */
	public GameSave() throws ClassNotFoundException, IOException {
		if (!SAVE_GAME_DIR.exists()) {
			SAVE_GAME_DIR.mkdir();
		}
		if (!SAVE_GAME_FILE.exists()) {
			SAVE_GAME_FILE.createNewFile();
		}
		Class.forName("org.sqlite.JDBC");
		Connection instanceCon = null;

		try {
			instanceCon = DriverManager.getConnection("jdbc:sqlite:"
					+ DATABASE_LOCATION);
			final Statement createStatement = instanceCon.createStatement();
			createStatement.executeUpdate(SAVEGAME_TABLE_CREATOR);
		} catch (SQLException e) {
			System.err.println(e);
		} finally {
			try {
				if (instanceCon != null) {
					instanceCon.close();
				}
			} catch (SQLException e) {
				System.err.println(e);
			}
		}
	}

	/**
	 * Overwrites the game
	 * 
	 * @param playerName the player name
	 * @param gameControl the control instance
	 * @param dateCreated the data
	 * @throws ClassNotFoundException a exception for where the class is not found
	 * @throws IOException exception with wrong input or output
	 */
	public void overwriteGame(String playerName, Control gameControl,
			String dateCreated) throws ClassNotFoundException, IOException {

		final Date now = new Date();
		ByteArrayOutputStream updateByteStream = null;
		ObjectOutputStream updateObjectStream = null;
		Class.forName("org.sqlite.JDBC");
		Connection updateCon = null;

		updateByteStream = new ByteArrayOutputStream();
		updateObjectStream = new ObjectOutputStream(updateByteStream);

		try {
			updateObjectStream.writeObject(gameControl);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			updateObjectStream.close();
		}

		try {
			updateCon = DriverManager.getConnection("jdbc:sqlite:"
					+ DATABASE_LOCATION);
			final PreparedStatement updateStatement = updateCon
					.prepareStatement(SAVEGAME_UPDATE_ENTRY + "NAME='"
							+ gameControl.getPlayer().getName() + "', "
							+ "SAVE_DATA=?, " + "CREATE_DATE='"
							+ SQLDATE.format(now) + "' WHERE NAME='"
							+ playerName + "' AND CREATE_DATE='" + dateCreated
							+ "'");
			updateStatement.setBytes(1, updateByteStream.toByteArray());
			updateStatement.executeUpdate();
		} catch (SQLException e) {
			System.err.println(e);
		} finally {
			try {
				if (updateCon != null) {
					updateCon.close();
				}
			} catch (SQLException e) {
				System.err.println(e);
			}
			updateByteStream.close();
		}
	}

	/**
	 * Delete the game
	 * 
	 * @param playerName name of the player
	 * @param dateCreated the data
	 * @throws ClassNotFoundException a exception for where the class is not found
	 */
	public void deleteGame(String playerName, String dateCreated)
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");
		Connection deleteCon = null;

		try {
			deleteCon = DriverManager.getConnection("jdbc:sqlite:"
					+ DATABASE_LOCATION);
			final Statement deleteStatement = deleteCon.createStatement();
			deleteStatement.executeUpdate(SAVEGAME_DELETE + "NAME='"
					+ playerName + "' AND CREATE_DATE='" + dateCreated + "'");
		} catch (SQLException e) {
			System.err.println(e);
		} finally {
			try {
				if (deleteCon != null) {
					deleteCon.close();
				}
			} catch (SQLException e) {
				System.err.println(e);
			}
		}
	}

	/**
	 * Load the game
	 * 
	 * @param playerName the player name
	 * @param dateCreated the data created
	 * @return the control
	 * @throws ClassNotFoundException a exception for where the class is not found
	 * @throws IOException exception with wrong input or output
	 */
	public Control loadGame(String playerName, String dateCreated)
			throws ClassNotFoundException, IOException {
		ObjectInputStream loadObjectStream = null;
		ByteArrayInputStream loadByteStream = null;
		Class.forName("org.sqlite.JDBC");
		Connection loadCon = null;
		Control loadControl = null;

		try {
			loadCon = DriverManager.getConnection("jdbc:sqlite:"
					+ DATABASE_LOCATION);
			final Statement loadStatement = loadCon.createStatement();
			loadStatement.setQueryTimeout(TIMEOUT);
			final ResultSet loadSet = loadStatement.executeQuery(SAVEGAME_LOAD
					+ "NAME='" + playerName + "' AND CREATE_DATE='"
					+ dateCreated + "'");
			loadByteStream = new ByteArrayInputStream(
					loadSet.getBytes("SAVE_DATA"));
			loadObjectStream = new ObjectInputStream(loadByteStream);
			loadControl = (Control) loadObjectStream.readObject();
		} catch (SQLException e) {
			System.err.println(e);
		} finally {
			try {
				if (loadCon != null) {
					loadCon.close();
				}
			} catch (SQLException e) {
				System.err.println(e);
			}
			loadByteStream.close();
			loadObjectStream.close();
		}
		return loadControl;
	}

	/**
	 * Save the game
	 * 
	 * @param gameControl control of the game
	 * @param playerName the player name
	 * @throws ClassNotFoundException a exception for where the class is not found
	 * @throws IOException exception with wrong input or output
	 */
	public void saveGame(Control gameControl, String playerName)
			throws ClassNotFoundException, IOException {
		final Date now = new Date();
		ByteArrayOutputStream saveByteStream = null;
		ObjectOutputStream saveObjectStream = null;
		Class.forName("org.sqlite.JDBC");
		Connection saveCon = null;

		saveByteStream = new ByteArrayOutputStream();
		saveObjectStream = new ObjectOutputStream(saveByteStream);
		try {
			saveObjectStream.writeObject(gameControl);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			saveObjectStream.close();
		}

		try {
			saveCon = DriverManager.getConnection("jdbc:sqlite:"
					+ DATABASE_LOCATION);
			final PreparedStatement saveStatement = saveCon
					.prepareStatement(SAVEGAME_SAVE + "('" + playerName
							+ "', ?, " + "'" + SQLDATE.format(now) + "'" + ")");
			saveStatement.setBytes(1, saveByteStream.toByteArray());
			saveStatement.executeUpdate();
		} catch (SQLException e) {
			System.err.println(e);
		} finally {
			try {
				if (saveCon != null) {
					saveCon.close();
				}
			} catch (SQLException e) {
				System.err.println(e);
			}
			saveByteStream.close();
		}
	}

	/**
	 * List of the entries
	 * 
	 * @return the list of the entries
	 * @throws ClassNotFoundException a exception for where the class is not found
	 */
	public String[] listOfEntries() throws ClassNotFoundException {
		final ArrayList<String> entryList = new ArrayList<String>();
		Class.forName("org.sqlite.JDBC");
		Connection listCon = null;

		try {
			listCon = DriverManager.getConnection("jdbc:sqlite:"
					+ DATABASE_LOCATION);
			final Statement loadStatement = listCon.createStatement();
			loadStatement.setQueryTimeout(TIMEOUT);
			final ResultSet loadSet = loadStatement
					.executeQuery(SAVEGAME_GET_ALL_ENTRIES);
			while (loadSet.next()) {
				entryList.add(loadSet.getString("NAME") + " "
						+ loadSet.getString("CREATE_DATE"));
			}
		} catch (SQLException e) {
			System.err.println(e);
		} finally {
			try {
				if (listCon != null) {
					listCon.close();
				}
			} catch (SQLException e) {
				System.err.println(e);
			}
		}
		return entryList.toArray(new String[entryList.size()]);
	}

	/**
	 * The location of the database
	 * 
	 * @return strin gof the location
	 */
	public String getDatabaseLocation() {
		return DATABASE_LOCATION;
	}

	/**
	 * Returns the name of the file
	 * 
	 * @return the name of the file
	 */
	@Override
	public String toString() {
		return "GameSave";
	}
}
